﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
using System.Diagnostics;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean FileExists(string filePath)
    {
        return new SqlBoolean(System.IO.File.Exists(filePath));
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean DirectoryExists(string directoryPath)
    {
        return new SqlBoolean(System.IO.Directory.Exists(directoryPath));
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString DirectoryName(string filePath)
    {
        if (System.IO.File.Exists(filePath))
            return new System.IO.FileInfo(filePath).DirectoryName.ToString();
        else
        {
            // Since the file doesn't actually exist, we'll do a best guess
            string[] parts = filePath.Split(System.IO.Path.DirectorySeparatorChar);
            string dirName = "";

            for (int i = 0; i < (parts.Length - 1); i++)
            {
                if (!dirName.Length.Equals(0))
                {
                    if (dirName.Substring(dirName.Length - 1, 1).Equals(":"))
                    {
                        dirName += System.IO.Path.DirectorySeparatorChar;
                    }
                }
                dirName = System.IO.Path.Combine(dirName, parts[i]);
            }

            return dirName;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Extension(string filePath)
    {
        if (System.IO.File.Exists(filePath))
        {
            string Ext = new System.IO.FileInfo(filePath).Extension.ToString();
            if (Ext.Substring(0, 1).Equals("."))
                return Ext;
            else
                return ("." + Ext);
        }
        else
        {
            string[] parts = filePath.Replace(DirectoryName(filePath).ToString(), "").Split(".".ToCharArray());
            if (parts.Length.Equals(1))
                return "";
            else
                return "." + parts[parts.Length - 1];
        }
    }

    #region " Table Value Function of Current Subdirectories in a directory "
    // http://nlsimmons.com/SQLScraps/?p=71

    [Microsoft.SqlServer.Server.SqlFunction(
        Name = "GetSubdirectories",
        FillRowMethodName = "GetSubdirectories_FillRow",
        TableDefinition = "subdirectory_name nvarchar(4000)")]
    public static IEnumerable GetSubdirectories(string directoryPath, bool recursive)
    {
        List<object[]> resultItems = new List<object[]>();

        if (System.IO.Directory.Exists(directoryPath))
            foreach (string filePath in System.IO.Directory.GetDirectories(directoryPath, "*", (recursive ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)))
            {
                object[] item = new object[1];
                FileInfo fi = new FileInfo(filePath);

                item.SetValue(filePath, 0);
                resultItems.Add(item);
            }

        return resultItems;
    }
    public static void GetSubdirectories_FillRow(Object obj, out SqlString filePath)
    {
        object[] item = (object[])obj;
        filePath = (SqlString)item[0].ToString();
    }

    #endregion

    #region " Table Value Function of Current Files in a directory "
    // http://nlsimmons.com/SQLScraps/?p=71

    [Microsoft.SqlServer.Server.SqlFunction(
        Name = "GetFiles",
        FillRowMethodName = "GetFiles_FillRow",
        TableDefinition = "physical_device_name nvarchar(4000)")]
    public static IEnumerable GetFiles(string directoryPath, bool recursive)
    {
        List<object[]> resultItems = new List<object[]>();

        if (System.IO.Directory.Exists(directoryPath))
            foreach (string filePath in System.IO.Directory.GetFiles(directoryPath, "*", (recursive ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)))
            {
                object[] item = new object[1];
                FileInfo fi = new FileInfo(filePath);

                item.SetValue(filePath, 0);
                resultItems.Add(item);
            }

        return resultItems;
    }
    public static void GetFiles_FillRow(Object obj, out SqlString filePath)
    {
        object[] item = (object[])obj;
        filePath = (SqlString)item[0].ToString();
    }

    #endregion
};

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetVolumeSpace(string serverName)
    {
        // See:  http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx
        if (serverName.Equals(string.Empty))
            serverName = Environment.MachineName;

        PerformanceCounterCategory pcc = new PerformanceCounterCategory("LogicalDisk", serverName);

        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData("Drive/MountPoint", SqlDbType.NVarChar, 256),
            new SqlMetaData("Capacity (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Used Space (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Free Space (MB)", SqlDbType.VarChar, 256),
            new SqlMetaData("Percent Free Space", SqlDbType.VarChar, 6)
        );

        SqlContext.Pipe.SendResultsStart(record);

        foreach (string instanceName in pcc.GetInstanceNames())
        {
            // Only want to see the values for "Total"
            if (instanceName != "_Total")
            {
                // Get a new object reference to the desired performance counters
                PerformanceCounter pcPercentFree = new PerformanceCounter("LogicalDisk", "% Free Space", instanceName, serverName);
                PerformanceCounter pcFreeMbytes = new PerformanceCounter("LogicalDisk", "Free Megabytes", instanceName, serverName);

                // Get the performance counter values
                float percentfree = pcPercentFree.NextValue();
                float freespace = pcFreeMbytes.NextValue();
                float capacity = (freespace * 100) / percentfree;
                float usedspace = capacity - freespace;

                // Close and free resources
                pcPercentFree.Close();
                pcPercentFree.Dispose();
                pcFreeMbytes.Close();
                pcFreeMbytes.Dispose();

                // Place values into the pipe to return to the caller
                record.SetSqlString(0, instanceName);
                record.SetSqlString(1, capacity.ToString());
                record.SetSqlString(2, usedspace.ToString());
                record.SetSqlString(3, freespace.ToString());
                record.SetSqlString(4, percentfree.ToString());
                SqlContext.Pipe.SendResultsRow(record);
            }
        }
        SqlContext.Pipe.SendResultsEnd();
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void DeleteFile(string filePath)
    {
        System.IO.File.Delete(filePath);
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MoveFile(string filePath, string newFilePath)
    {
        System.IO.File.Move(filePath, newFilePath);
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void DeleteDirectory(string directoryPath, bool recursive)
    {
        System.IO.Directory.Delete(directoryPath, recursive);
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void MoveDirectory(string directoryPath, string newDirectoryPath)
    {
        System.IO.Directory.Move(directoryPath, newDirectoryPath);
    }
};
